package com.ocom.common.utils.excel;

/**
 * @Description: TODO
 * @Author: 朱晖
 * @Date: 2022/5/17
 **/

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;

public class SXSSFWORKBookUtils {

	@SuppressWarnings("resource")
	public static void main(String[] args) throws FileNotFoundException, InvalidFormatException {
		long startTime = System.currentTimeMillis();
		String filePath = "E:\\txt\\123.xlsx";

		//创建一个文件
		try {
			//创建Excel文件薄
			HSSFWorkbook workbook=new HSSFWorkbook();
			//创建工作表sheeet
			workbook.createSheet();

			File file=new File(filePath);

			//判断文件或文件夹是否存在
			boolean flag = file.exists();
			if(flag)
			{
				//文件存在就要删除文件
				file.delete();
			}

			file.createNewFile();
			FileOutputStream stream= FileUtils.openOutputStream(file);
			workbook.write(stream);
			stream.close();
		}catch (Exception e){
			e.printStackTrace();
		}finally {

		}

		SXSSFWorkbook sxssfWorkbook = null;
		BufferedOutputStream outputStream = null;
		try {
			//这样表示SXSSFWorkbook只会保留100条数据在内存中，其它的数据都会写到磁盘里，这样的话占用的内存就会很少
			sxssfWorkbook = new SXSSFWorkbook(getXSSFWorkbook(filePath),100);
			//获取第一个Sheet页
			SXSSFSheet sheet = sxssfWorkbook.getSheetAt(0);
			for (int i = 0; i < 50; i++) {
				for (int z = 0; z < 10000; z++) {
					SXSSFRow row = sheet.createRow(i*10000+z);
					for (int j = 0; j < 10; j++) {
						row.createCell(j).setCellValue("你好："+j);
					}
				}
			}
			outputStream = new BufferedOutputStream(new FileOutputStream(filePath));
			sxssfWorkbook.write(outputStream);
			outputStream.flush();
			sxssfWorkbook.dispose();// 释放workbook所占用的所有windows资源
		} catch (IOException e) {
			e.printStackTrace();
		}finally {
			if(outputStream!=null) {
				try {
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		long endTime = System.currentTimeMillis();
		System.out.println(endTime-startTime);
	}


	/**
	 * 先创建一个XSSFWorkbook对象
	 * @param filePath
	 * @return
	 */
	public static XSSFWorkbook getXSSFWorkbook(String filePath) {
		XSSFWorkbook workbook =  null;
		BufferedOutputStream outputStream = null;
		try {
			File fileXlsxPath = new File(filePath);
			outputStream = new BufferedOutputStream(new FileOutputStream(fileXlsxPath));
			workbook = new XSSFWorkbook();
			workbook.createSheet("测试Sheet");
			workbook.write(outputStream);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(outputStream!=null) {
				try {
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return workbook;
	}

}

